Using a Spreadsheet to Determine Bond Price Volatility
By Fred Shipley, Ph.D.
Computerized Investing, September/October 1988
The volatility in the stock market over the past year caused many investors to consider fixed-income investments as a means of reducing variability in their portfolios. For some investors, however, the fairly steady climb in interest rates since the market crash has led to an erosion in portfolio values. To help you evaluate the volatility inherent in fixed-income investments, we are presenting in this issue a spreadsheet template that allows you to calculate bond values based on knowledge of the coupon rate, the length of time to maturity and the current market rate of interest. (All of these terms and the basics of bond pricing are explained later.) In addition, you will be able to see how bond prices can change with changes in market interest rates. In particular, you can examine (with a little extra work) the effects of changing interest rates on the total value of a bond portfolio, including a bond mutual fund.
Fixed-income securities are investments that make a promise to pay a specific dollar return for some fixed period of time. We usually refer to longer-term fixed-income investments as bonds, and problems of volatilityare more relevant to intermediate- and long-term investments. Short-term fixed-income investments are those with a maturity of no more than one year, and they are usually called money market investments. Intermediate-term investments have maturities from one to seven years, and long-term bonds have maturities of more than seven years. The longest term bonds rarely have maturities of more than 30 years. These definitions correspond with the SEC requirements for descriptions applied to bond mutual funds. Since the dollar payments are usually fixed when the bonds are first issued, a change in market interest rates will cause a change in the market value of the bonds.
The first rule of bond price changes is the inverse relation between market values and market interest rate changes. If market interest rates go up, the value of the fixed interest and principal payments you expect to receive goes down. Conversely, if interest rates fall, the value of the bonds will increase. Put simply, the bond market rallies when interest rates fall.
Two factors are of primary importance in determining the degree of volatility or price sensitivity of a bond investment -- the coupon rate and the time to maturity. The coupon rate is the interest rate on the bond that is established at the time of issuance. The higher the coupon rate, the less the value of the bond will change in response to interest rate changes. Then again, the lower the coupon rate, the more the value of the bond will vary with changes in market interest rates. Essentially, the higher coupon provides more cash flow and provides it earlier, so it acts as an anchor reducing the impact of interest rate changes on price changes.
Maturity is the second important factor affecting the degree of price volatility. The longer the time before a bond matures, the more sensitive its value will be to interest rate changes. Since bond values change inversely to the change in interest rates, a long-term, low-coupon- rate bond will suffer a very great decrease in value when interest rates increase; a short-term, high-coupon rate bond will change relatively little in value. The longer-term maturity means that it takes longer for investors to recover their initial investment and so the market value of the bond is more sensitive to changes in market interest rates.
Frequently maturities and coupon rates available in the market have offsetting effects. For example, from the late 1970s to the early 1980s when interest rates were quite high, newly issued bonds carried high coupon rates (which mean less volatility) and had longer maturities (which means greater volatility) than bonds issued several years previously. Consequently it is important to have a measure of price sensitivity to interest rate changes that takes account of both maturity and coupon effects. Financial analysts use such a measure of price volatility -- it is known as duration. Duration is a weighted- average time measure. Using present value weightings, in which later cash flows are less valuable than earlier cash flows, it indicates a weighted-average maturity. Because of this weighting scheme, we can say that, regardless of the coupon rate and the maturity of different bonds, we can say that a bond with longer duration will be more volatile than one with the shorter duration. Thus duration is directly related to price volatility caused by interest rate changes. Unfortunately, duration is a fairly complicated concept, both in terms of intuition and mathematics.
Basics of Bond Valuation
The value of a bond is simply the present value of the cash payments through maturity to the bondholder. In the case of a bond with a fixed coupon rate, this stream of cash payments is relatively easy to determine. There may be uncertainty whether the issuer will in fact make all the payments as required, but at least there is a legal commitment to make certain payments -- interest at specified intervals and repayment of principal at maturity. The only other ingredient necessary to complete the valuation is the appropriate market rate of interest. This market rate of interest is the current return being offered on bonds of similar risk and maturity. We won't worry in detail about that now, since we are simply interested in understanding the effect of changing rates on value. All we must do is use an estimate of the future level of rates. This can be something we get from reading an economic forecast in the paper or an estimate based on our own evaluation of economic and market conditions.
We will start by assuming that our valuation is done at an interest payment date. In the case of a typical bond, this means that we will not receive another payment for 6 months (since almost all corporate bonds make semi-annual interest payments).
Most spreadsheets provide a present value function that is quite handy for this valuation. The @PV function in 1-2-3 computes the present value of a series of equal payments if you specify the amount of the payment, the appropriate interest rate to use and the number of periods the payments will be made. This @PV function, however, only works for constant dollar payments, and a bond does not quite have only constant dollar payments. If you hold the bond to maturity the last payment received will include the repayment of principal as well as the final payment of interest. We can get around this problem by treating the interest and principal payments separately.
Necessary Data Inputs for Bond Template
The data necessary to compute duration is entered in rows five through nine and includes the number of years to maturity (15 1/2 years, for example is entered as 15.5), the principal amount ($1,000, except in special cases), the coupon rate (enter the rate as a decimal), and the market interest rate (you can get a reasonable number to use from Barron's Market Laboratory section if you know the bond's rating). The BONDIRR template deals with more complicated situations in which your have to calculate values in between interest payment dates.
In rows 11 through 15, some useful values are calculated. The formula for determining the market value is in cell C13. The first part of this equation computes the present value of the interest payments, the second part computes the present value of the repayment of principal. (The present value function in 1-2-3 computes the present value of a stream of payments; this is somewhat different from commonly used terminology.) It might be tempting to simply refer to cell C11, where the semi-annual interest payments are calculated, instead of $C$7/2*$C$6 in the first part of the @PV function. This was not done however, because the formula is to create a two-way data table. Lotus allows only one input formula for determining the values in a two-way data table and making the reference to C11 there would result in all the values staying at $1,000. None of those values would change no matter what you did to the data table.
Establishing a Data Table to Determine Price Sensitivity
Rather than dealing with the complications of duration now, the data table (sensitivity analysis) capability of 1-2-3 was used to see the effect of changing interest rates and maturity on values. This is particularly useful for dealing with portfolios of bonds, as long as we can determine the average maturity and average coupon on the portfolio. We will deal with those issues later.
Setting up the data table was a two step process. First a table was created for bond prices, then we that table was used to make a table of percentage price changes. This latter table can be used to create some 1- 2-3 graphs illustrating the price sensitivity.
The important things to remember in setting a data table are that the formula used to create the values must appear in the upper left hand corner, the first data input values are those along the left hand side of the table, and the second input values are along the top of the table. Finally, remember that the table range must include the bordering rows and columns.
If you change the input variables, you can update the data table by simply pressing the F8 (table) key. 1-2-3 automatically updates the most recently created table. If you are using more than one data table, you will have to manually recreate the older table.
While the tables in the bond template are not very big, it may take a while to recalculate, especially on a regular IBM PC or clone. More complicated data tables can take 15 to 20 minutes or more to recalculate. Be prepared to deal with these delays. It is better to make all the changes you want to make before recalculating large data tables.
To simplify the evaluation of the degree of price sensitivity, the first table of prices was used to a separate table that gives percentage price changes. Looking at percentage changes allows you to make easier comparisons between bonds or bond portfolios of different coupons and maturities.
Some Further Information about Duration
Since duration indicates price sensitivity, we included the formula to calculate in cell C14. For any coupon-bearing bond, duration is always less than the bond's term to maturity and it tends to reach a limit, which is related not to maturity, but rather to the current market interest rate. This limit is given by the formula:
1
Duration Limit= 1 + ----------
YTM
where: YTM is the bond's market yield.
For the a bond with a market interest rate of 8%, this formula gives us:
1
Duration Limit = 1 + -------
.08
= 1 + 12.5
= 13.5 yrs.
So a perpetual bond with an 8% coupon and 8% market yield has a duration that is only 13.5 years.
If you are evaluating a number of bonds or bond portfolios, you may not want to create detailed price sensitivity tables for each one. In this case, using the duration formula will enable you to get a rough feel for the sensitivity. You can then perform the more detailed analysis on a smaller number of bonds.
Using Duration to Analyze Price Sensitivity
While we know that longer duration means greater price sensitivity to market interest rate changes, nothing we have done so far indicates the magnitude of the effect. For relatively small market interest rate changes, the formula below is a useful approximation of the percentage change in bond price:
Approximate Change in market rate
% = -(Duration) x -----------------------
Price Change (1 + market rate)
As an example, consider the bond whose characteristics appear in Figure 2 and suppose we expect interest rates to increase from 8% to 9%. According to the formula, the approximate percentage price change would be
Approximate (.09 - .08)
% = -(8.99) -----------
Price Change (1 + .08)
= -(8.99) x (.0093)
= -.0832 or
= -8.32%
From the bond template we can see that the actual percentage price change is -8.14%.
If interest rates were to decrease from 8% to 7%, this approximation formula would give us an 8.32% increase, whereas the actual percentage increase is 9.20%. This illustrates the approximation that is part of this formula, but it makes for some quick calculations. This also illustrates another important feature of bond price sensitivity. Changes in value are not symmetric for market interest rate increases and decreases. A 1% increase in rates will always give a smaller percentage decrease in value than the percentage increase in value from a 1% decrease in rates. In this way investors have a little protection if they guess wrong about the direction of changes in market rates.
